package com.boxtelecom.db;

import java.util.ArrayList;
import java.util.Iterator;

import com.boxtelecom.users.FacebookUser;
import com.boxtelecom.users.ListFriend;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class FriendDBAdapter {

	private static final String DATABASE_TABLE = "friends";
	private static final int DATABASE_VERSION = 1;
	
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_BIRTHDAY = "birthday";
	private static final String KEY_SIGNE = "signe";
	private static final String KEY_FJID = "fjid";
	private static final String KEY_PICTURE = "picture";
	private static final String KEY_TIME = "time";
	
	private SQLiteDatabase _db;
	private final Context _context;
	private MyCircleDBOpenHelper _dbHelper;
	
	public FriendDBAdapter(Context aContext, String db) {
		super();
		this._context = aContext;
		_dbHelper = new MyCircleDBOpenHelper(aContext, db, null, DATABASE_VERSION);
	}
	
	/**
	 * open the table 'friend'
	 */
	public void open(){
		try {
			_db = _dbHelper.getWritableDatabase();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			_db = _dbHelper.getReadableDatabase();
		}
	}
	
	/**
	 * close the table 'friend'
	 */
	public void close(){
		_db.close();
	}
	
	/**
	 * Check the database comparing with the list of friend
	 * add or delete friend to match to the list
	 * if the table does not exist, creates it
	 * @param list
	 * 			list of friend to compare
	 * @return String the action done (create list, add or delete)
	 */
	public int check(ArrayList<String> list){
		int count = getCount();
		if(count>0){
			int size = list.size();
			Log.d("db", "size: " + String.valueOf(size) + " ... count " + String.valueOf(count));
			if(size == count){
				return 0;
			} else {
				if(size > count){
					return 1;
				} else {
					deleteSurplusFriends(list);
					return 2;
				}
			}
		} else {
			return 3;
		}
	}
	
	/**
	 * get the number of rows contained by the table 'friend'
	 * @return the number of rows in the table 'friend'
	 */
	public int getCount(){
		Cursor cursor = selectAll();
		int count = cursor.getCount();
		cursor.close();
		return count;
	}
	
	/**
	 * Return the list of friend stored in the database
	 * @return ListFriend
	 */
	public ListFriend getFacebookFriend(){
		Cursor cursor = _db.rawQuery("select * from " + DATABASE_TABLE, null);
		ListFriend list = null;
		
		if(cursor.moveToFirst()){
			list = new ListFriend();
			do{
				int idIndex = cursor.getColumnIndex(KEY_ID);
				String id = cursor.getString(idIndex);;
				
				int nameIndex = cursor.getColumnIndex(KEY_NAME);
				String name = cursor.getString(nameIndex);
				
				int birthdayIndex = cursor.getColumnIndex(KEY_BIRTHDAY);
				String birthday = cursor.getString(birthdayIndex);
				
				int signeIndex = cursor.getColumnIndex(KEY_SIGNE);
				String signe = cursor.getString(signeIndex);
				
				int fjidIndex = cursor.getColumnIndex(KEY_FJID);
				String fjid = cursor.getString(fjidIndex);
				
				int pictureIndex = cursor.getColumnIndex(KEY_PICTURE);
				String picture = cursor.getString(pictureIndex);
				
				int timeIndex = cursor.getColumnIndex(KEY_TIME);
				String time = cursor.getString(timeIndex);
				
				FacebookUser user = new FacebookUser(id, name, birthday, signe, fjid);
				user.setPicturePath(picture);
				user.setUpdatedTime(time);
				
				list.add(user);
				
			} while(cursor.moveToNext());
		}
		
		cursor.close();
		list.setSize(cursor.getCount());
		return list;
	}
	

	/**
	 * retreive an user into the table 'friend'
	 * @param where the id of the use
	 * @return the wanted user
	 */
	public FacebookUser getFriend(String where) {
		// TODO Auto-generated method stub
		Cursor cursor = _db.rawQuery("select * from " + DATABASE_TABLE + " where " + KEY_ID + " = " + where, null);
		FacebookUser user = new FacebookUser();
		
		if(cursor.moveToFirst()){
				int idIndex = cursor.getColumnIndex(KEY_ID);
				String id = cursor.getString(idIndex);
				
				int nameIndex = cursor.getColumnIndex(KEY_NAME);
				String name = cursor.getString(nameIndex);
				
				int birthdayIndex = cursor.getColumnIndex(KEY_BIRTHDAY);
				String birthday = cursor.getString(birthdayIndex);
				
				int signeIndex = cursor.getColumnIndex(KEY_SIGNE);
				String signe = cursor.getString(signeIndex);
				
				int fjidIndex = cursor.getColumnIndex(KEY_FJID);
				String fjid = cursor.getString(fjidIndex);
				
				int pictureIndex = cursor.getColumnIndex(KEY_PICTURE);
				String picture = cursor.getString(pictureIndex);
				
				int timeIndex = cursor.getColumnIndex(KEY_TIME);
				String time = cursor.getString(timeIndex);
				
				user.setId(id);
				user.setName(name);
				user.setBirthday(birthday);
				user.setSigneAstro(signe);
				user.setFjid(fjid);
				user.setPicturePath(picture);
				user.setUpdatedTime(time);
				
				user.print();
		}
		
		cursor.close();	
		
		return user;
	}
	
	/**
	 * insert all the user into the table 'friend'
	 * @param all the friend
	 */
	public void insertFriends(ListFriend list){
		Iterator<FacebookUser> it = list.getIterator();
		
		while(it.hasNext()){
			insertFriend(it.next());
		}
	}
	
	/**
	 * insert the specifed into the table 'friend'
	 * @param friend the friend to insert in the database
	 * @return true if it has been successfull, else false
	 */
	private boolean insertFriend(FacebookUser friend){	
		ContentValues newFriend = new ContentValues();
		newFriend.put(KEY_ID, friend.getId());
		newFriend.put(KEY_NAME, friend.getName());
		newFriend.put(KEY_BIRTHDAY, friend.getBirth());
		newFriend.put(KEY_SIGNE, friend.getSigneAstro());
		newFriend.put(KEY_FJID, friend.getFjid());
		newFriend.put(KEY_PICTURE, friend.getPicturePath());
		newFriend.put(KEY_TIME, friend.getUpdatedTime());
		
		return _db.insert(DATABASE_TABLE, null, newFriend) > 0;
	}
	
	/**
	 * remove from the table 'friend', the specified friend
	 * @param idFriend the id of the friend to delete
	 * @return the number of rows deleted
	 */
	private int deleteFriend(String idFriend) {
		// TODO Auto-generated method stub
		return _db.delete(DATABASE_TABLE, KEY_ID + "=" + idFriend, null);		
	}
	
	/**
	 * update a friend into the table 'friend'
	 * @param friend the friend to update
	 */
	public void updateFriend(FacebookUser friend) {
		// TODO Auto-generated method stub
		ContentValues updatedValues = new ContentValues();
		
		updatedValues.put(KEY_NAME, friend.getName());
		updatedValues.put(KEY_SIGNE, friend.getSigneAstro());
		updatedValues.put(KEY_BIRTHDAY, friend.getBirth());
		updatedValues.put(KEY_FJID, friend.getFjid());
		updatedValues.put(KEY_TIME, friend.getUpdatedTime());
		
		String where = KEY_ID + "=" + friend.getId();
		
		_db.update(DATABASE_TABLE, updatedValues, where, null);
	}
	
	/**
	 * delete all of the rows of the database
	 * @return return true if it has succeed
	 */
	public boolean deleteAll(){
		return _db.delete(DATABASE_TABLE, null, null) > 0;
	}
	
	/**
	 * select all the from of the table 'friend'
	 * @return a cursor with all the rows of the table 'friend'
	 */
	private Cursor selectAll(){
		return _db.rawQuery("select * from " + DATABASE_TABLE, null);
	}
	
	/** 
	 * print all the rows of the database
	 */	
	public void printAll(){
		Cursor cursor = _db.rawQuery("select * from " + DATABASE_TABLE, null);
		if(cursor.moveToFirst()){
			do{
				int idIndex = cursor.getColumnIndex(KEY_ID);
				String id = cursor.getString(idIndex);;
				
				int nameIndex = cursor.getColumnIndex(KEY_NAME);
				String name = cursor.getString(nameIndex);
				
				int birthdayIndex = cursor.getColumnIndex(KEY_BIRTHDAY);
				String birthday = cursor.getString(birthdayIndex);
				
				int signeIndex = cursor.getColumnIndex(KEY_SIGNE);
				String signe = cursor.getString(signeIndex);
				
				int fjidIndex = cursor.getColumnIndex(KEY_FJID);
				String fjid = cursor.getString(fjidIndex);
				
				int pictureIndex = cursor.getColumnIndex(KEY_PICTURE);
				String picture = cursor.getString(pictureIndex);
				
				int timeIndex = cursor.getColumnIndex(KEY_TIME);
				String time = cursor.getString(timeIndex);
				
				Log.d("Database", id + " " + name + " " + birthday + " " + signe + " " + fjid + " " + picture + " " + time);
			} while(cursor.moveToNext());
		} else {
			Log.d("Database", "table " + DATABASE_TABLE + " empty");
		}
		
		Log.d("Database", String.valueOf(cursor.getCount()) + " rows in database friend");
		
		cursor.close();
	}
	
	/**
	 * detect and insert friends in the database that are
	 * in the list of friend but not in the database 
	 * @param list
	 * 			list of all friend containing the missing ones
	 */
	public void insertMissingFriends(ListFriend list) {
		// TODO Auto-generated method stub
		ListFriend friends = new ListFriend();
		
		friends = getFacebookFriend();
		
		Iterator<FacebookUser> itList = list.getIterator();
		
		while(itList.hasNext()){
			FacebookUser tmpList = itList.next();
			
			if(!friends.idExist(tmpList.getId())){
				insertFriend(tmpList);
			}
		}
	}
	
	/**
	 * detect and delete friends from the database that are
	 * in the database but not in the list of friend
	 * @param list
	 * 			list of all friend to find the surplus ones
	 */
	private void deleteSurplusFriends(ArrayList<String> list) {
		// TODO Auto-generated method stub
		ListFriend friends = new ListFriend();
		friends = getFacebookFriend();
		int i = 0;
		
		Iterator<FacebookUser> it = friends.getIterator();

		while(it.hasNext()){
			FacebookUser tmp = it.next();			
			if(!list.contains(tmp.getId())){
				i = i + deleteFriend(tmp.getId());
			}
		}
	}

	private static class MyCircleDBOpenHelper extends SQLiteOpenHelper{
		
		private static final String DATABASE_CREATE = "create table " + DATABASE_TABLE + " (" +
		KEY_ID + " VARCHAR(20), " + KEY_NAME + " VARCHAR(20), " + KEY_BIRTHDAY + " VARCHAR(20), " +
		KEY_SIGNE + " VARCHAR(20), " + KEY_FJID + " VARCHAR(20), " + KEY_PICTURE + " VARCHAR(20), " + KEY_TIME + " VARCHAR(20));";

		public MyCircleDBOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			Log.d("debug", "friend db adatper on create");
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			onCreate(db);
		}		
	}
}
